<?php 

class Data{
    public static $db;
    public static $db_host = "127.0.0.1";
    public static $db_user = "root";
    public static $db_pass = "root";
    public static $db_name = "db_data";

    public static $db_table = "db_data";

    public static $likely_bots;
    public static $countRepeat;
    public static $data;

    public static function start(){
        try{
            self::$db = new \PDO(
                "mysql:host=".self::$db_host .
                ";dbname=".self::$db_name,
                self::$db_user,
                self::$db_pass
            );
            self::load();
        } catch(PDOException $e){
            self::$db  = null;
            require_once "src/errorConnectDB.php";
        }
    }

    private static function load(){
        self::checkBots();
        self::countRepeat();
        self::getUniqueData();
        require_once "src/index.php";
    }

    private static function getUniqueData(){
        $query = self::$db->query("
            SELECT 
            img_id, 
            JSON_ARRAYAGG(ip) as ips,
            COUNT(ip) as count
            FROM db_data
            GROUP BY img_id;
        ");
        while($row = $query->fetch(PDO::FETCH_ASSOC)){
            self::$data[] = [
                "img_id"  => $row['img_id'],
                "ips"     => array_unique(json_decode($row['ips'])),
                "count"   => $row['count'],
                "repeats" => self::$countRepeat[$row['img_id']]
            ];
        }
    }

    private static function checkBots(){
        $query = self::$db->query("
            SELECT ip, COUNT(*) as count, JSON_ARRAYAGG(img_id) as imgs
            FROM db_data
            WHERE ip IN (
                SELECT ip
                FROM db_data
                GROUP BY ip
                HAVING COUNT(*) > 1
            )
            GROUP BY ip;
        ");
        while($row = $query->fetch(PDO::FETCH_ASSOC)){
            self::$likely_bots[$row['ip']] = $row;
        }
    }

    private static function countRepeat(){
        $query = self::$db->query("
            SELECT 
            img_id, 
            COUNT(*) as count,
            JSON_ARRAYAGG(img_id) as ids,
            JSON_ARRAYAGG(ip) as ips
            FROM (
                SELECT img_id, ip FROM db_data
                WHERE 
                    ip IN (SELECT ip FROM db_data GROUP BY ip HAVING COUNT(*) > 1) GROUP BY img_id, ip
                ) AS subquery
            GROUP BY 
                img_id
        ");
        while($row = $query->fetch(PDO::FETCH_ASSOC)){
            self::$countRepeat[$row['img_id']] = $row['count'];
        }
    }
}

Data::start();